package com.puyuan.dao.impl;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.puyuan.dao.StudentDao;
import com.puyuan.domain.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private QueryRunner qr = new QueryRunner(new ComboPooledDataSource());

    /**
     * 添加学生
     * @param s
     * @return
     */
    @Override
    public int addStu(Student s) {
        String sql = "insert into stu values(null,?,?,?,?,?,?)";
        Object[] params = {s.getName(), s.getGender(), s.getHobby(), s.getBirthday(), s.getIntro(), s.getPhoto()};
        int row = 0;
        try {
            row = qr.update(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }

    /**
     * 查询学员信息
     */
    @Override
    public List<Student> findStu(int currentPage,int pageSize,String name1,String gender1) {
        //分页查询 [可变的  无条件的时候 有条件的时候先查询在分页]
        String sql = "";
        StringBuilder sb = new StringBuilder("select * from stu where 1=1");
        //判断name1和gender1是否有条件
        if (name1 != null && !"".equals(name1)){
            sb.append(" and name like '%" + name1 + "%'");
        }
        if (gender1 != null && !"".equals(gender1)){
            if ("0".equals(gender1)){
                gender1 = "男";
            }else {
                gender1 = "女";
            }
            sb.append(" and gender = '" + gender1 + "'");
        }
        sb.append(" limit ? , ?");
        sql = sb.toString();
        List<Student> list = null;
        try {
            list = qr.query(sql, new BeanListHandler<>(Student.class),(currentPage-1)*pageSize,pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 查询学员的总数
     * @return
     */
    @Override
    public int findStuCount(String name1,String gender1) {
        String sql = "";
        StringBuilder sb = new StringBuilder("select count(*) from stu where 1=1");
        //判断name1和gender1是否有条件
        if (name1 != null && !"".equals(name1)){
            sb.append(" and name like '%" + name1 + "%'");
        }
        if (gender1 != null && !"".equals(gender1)){
            if ("0".equals(gender1)){
                gender1 = "男";
            }else {
                gender1 = "女";
            }
            sb.append(" and gender = '" + gender1 + "'");
        }
        sql = sb.toString();
        Long count = null;
        try {
            count = (Long) qr.query(sql, new ScalarHandler<>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        int c = count.intValue();
        return c;
    }

    /**
     * 删除学员信息
     * @param ids
     * @return
     */
    @Override
    public int deleteStu(String ids) {
        String sql = "delete from stu where sid in ("+ ids +")";
        int row = 0;
        try {
            row = qr.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }

    /**
     * 根据id查询对应学生信息
     * @param id
     * @return
     */
    @Override
    public Student findStuById(int id) {
        String sql = "select * from stu where sid = ?";
        Student student = null;
        try {
            student = qr.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return student;
    }

    /**
     * 修改指定的学生信息
     * @param s
     * @return
     */
    @Override
    public int modifyStu(Student s) {
        String sql = "update stu set name = ?,gender = ?,hobby = ?,birthday = ?,intro = ?,photo = ? where sid = ?";
        Object[] parmas = {s.getName(),s.getGender(),s.getHobby(),s.getBirthday(),s.getIntro(),s.getPhoto(),s.getSid()};
        int row = 0;
        try {
            row = qr.update(sql, parmas);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }
}
